package com.cpi.cornpp.futurepurchase.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.futurepurchase.dto.FuturePurchaseDTO;
import com.cpi.cornpp.futurepurchase.dto.FuturePurchaseRealizedDTO;
import com.cpi.cornpp.futurepurchase.dto.FuturePurchaseSearchCriteriaDTO;
import com.cpi.cornpp.futurepurchase.exception.FuturePurchaseException;
import com.cs.common.init.SystemParameterCollection;
import com.cs.common.init.beans.SystemParameterBean;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class FuturePurchaseDAO extends DBDAO {
	/** SQL insert FuturePurchase */
	private static final String SQL_INSERT_FUTURE_PURCHASE = "INSERT INTO corn_futures (application_future_period_id, account_future_period_id, reference, quantity, purchase_date, unit_price, corn_origin_id) VALUES (?, ?, ?, ?, ?, ?,?)";
	/** SQL select FuturePurchase by period range */
	private static final String SQL_SELECT_FUTURE_PURCHASES_BY_PERIOD_RANGE = "SELECT corn_futures.corn_future_id, corn_futures.application_future_period_id, account_future_period_id, reference, corn_futures.quantity, purchase_date, unit_price, updated, periodApp.short_name + ' ' + convert( varchar, periodApp.year ), periodAcc.short_name + ' ' + convert( varchar, periodAcc.year ), 0, unit_price * ( ( corn_futures.quantity / (select qty from units where unit_id = 'CON') ) * (select qty from units where unit_id = 'BUS') ), (select description from corns_origins where corn_origin_id = corn_futures.corn_origin_id )  FROM corn_futures inner join futures_periods periodApp on periodApp.future_period_id = corn_futures.application_future_period_id inner join futures_periods periodAcc on periodAcc.future_period_id = corn_futures.account_future_period_id left join corn_futures_distributions cfd on cfd.corn_future_id = corn_futures.corn_future_id left join vw_customers cus on cfd.customer_id = cus.customer_id left join items_distributions id on id.corn_future_distribution_id = cfd.corn_future_distribution_id left join vw_items ite on id.item_id = ite.item_id WHERE account_future_period_id in (select future_period_id from futures_periods where year >= ? and year <= ?)";
	/** SQL filter to futures without customer */
	private static final String SQL_FILTER_WITHOUT_CUSTOMER = " and not exists(select cfd.corn_future_id from corn_futures_distributions cfd where cfd.corn_future_id = corn_futures.corn_future_id)";
	/** SQL filter to futures without product */
	private static final String SQL_FILTER_WITHOUT_PRODUCT = " and not exists(select cfd.corn_future_id from corn_futures_distributions cfd inner join items_distributions id on cfd.corn_future_distribution_id = id.corn_future_distribution_id where cfd.corn_future_id = corn_futures.corn_future_id)";
	/** SQL filter to futures customer */
	private static final String SQL_FILTER_CUSTOMER = " and (cus.customer_id like ? or cus.description like ?) ";
	/** SQL filter to futures product */
	private static final String SQL_FILTER_PRODUCT = " and (ite.item_id like ? or ite.description like ?) ";
	/** SQL select FuturePurchase by id */
	private static final String SQL_SELECT_FUTURE_PURCHASES_BY_ID = "SELECT corn_future_id, application_future_period_id, account_future_period_id, reference, quantity, purchase_date, unit_price, updated, ' ', ' ', 0, 0, corn_origin_id FROM corn_futures WHERE corn_future_id = ?";
	/** SQL select para obtener los futuros junto con su disponible */
	private static final String SQL_SELECT_FUTURE_PURCHASES_BY_AVAILABLES = "SELECT corn_futures.corn_future_id, corn_futures.application_future_period_id, account_future_period_id, corn_futures.reference, corn_futures.quantity, purchase_date, unit_price, updated, periodApp.short_name + ' ' + convert( varchar, periodApp.year ), periodAcc.short_name + ' ' + convert( varchar, periodAcc.year ), (afutures.quantity - futures_used) available, poFutures.quantity, isnull( poFutures.corn_future_id, -1 ), unit_price  FROM corn_futures inner join futures_periods periodApp  on periodApp.future_period_id = corn_futures.application_future_period_id  inner join futures_periods periodAcc  on periodAcc.future_period_id = corn_futures.account_future_period_id left join purchases_orders_futures poFutures on corn_futures.corn_future_id = poFutures.corn_future_id and poFutures.purchase_order_id = ? inner join vw_available_futures afutures on afutures.corn_future_id = corn_futures.corn_future_id and afutures.quantity + isnull( poFutures.quantity, 0 ) - afutures.futures_used > 0";
	/** SQL update FuturePurchase by id */
	private static final String SQL_UPDATE_FUTURE_PURCHASE = " UPDATE corn_futures set application_future_period_id = ?, account_future_period_id =?, reference = ?, quantity =?, purchase_date=?, unit_price=?, corn_origin_id = ?, updated = getdate() where corn_future_id = ?";
	/** SQL delete item distribution */
	private static final String SQL_DELETE_ITEM_DISTRIBUTION_BY_CUSTOMER = " delete from items_distributions where corn_future_distribution_id = ?";
	/** SQL delete customer distribution */
	private static final String SQL_DELETE_CUSTOMER_DISTRIBUTION_BY_CUSTOMER = " delete from corn_futures_distributions where corn_future_distribution_id = ? ";
	/** SQL delete future purchase */
	private static final String SQL_DELETE_FUTURE_PURCHASE = " delete from corn_futures where corn_future_id = ? ";
	/** SQL para seleccionar un futuro por cliente */
	private static final String SQL_SELECT_FUTURE_PURCHASES_BY_DISTRIBUTION_ID = "SELECT corn_futures.corn_future_id as corn_future_id, application_future_period_id, account_future_period_id, reference, corn_futures.quantity as quantity, purchase_date, unit_price, updated, ' ', ' ', 0, 0, corn_origin_id FROM corn_futures, corn_futures_distributions WHERE corn_futures.corn_future_id = corn_futures_distributions.corn_future_id and corn_future_distribution_id = ?";
	private static final String SQL_SELECT_FUTURE_PURCHASES_REALIZED = "SELECT corn_futures.corn_future_id, corn_futures.application_future_period_id, account_future_period_id, reference, corn_futures.quantity, purchase_date, unit_price, corn_futures.updated, periodApp.short_name + ' ' + convert( varchar, periodApp.year ), periodAcc.short_name + ' ' + convert( varchar, periodAcc.year ), 0 from dbo.realizeds_instructions ri inner join corn_futures on ri.corn_future_id = corn_futures.corn_future_id inner join futures_periods periodApp on periodApp.future_period_id = corn_futures.application_future_period_id inner join futures_periods periodAcc on periodAcc.future_period_id = corn_futures.account_future_period_id left join corn_futures_distributions cfd on cfd.corn_future_id = corn_futures.corn_future_id left join vw_customers cus on cfd.customer_id = cus.customer_id left join items_distributions id on id.corn_future_distribution_id = cfd.corn_future_distribution_id left join vw_items ite on id.item_id = ite.item_id WHERE ri.purchase_instruction_future_id = ?";
	private static final String SQL_INSERT_REALIZED_PURCHASES = "INSERT INTO realizeds_instructions (corn_future_id,purchase_instruction_future_id,user_id,updated) VALUES (?,?,?,getDate())";
	private static final String SQL_GET_REMAINING_TONS = "select  pid.corn_quantity - sum(isnull(id.quantity,0)) from dbo.purchases_instructions_details pid inner join dbo.purchases_instructions_futures pif on pid.purchase_instruction_detail_id = pif.purchase_instruction_detail_id left join realizeds_instructions ri on  pif.purchase_instruction_future_id = ri.purchase_instruction_future_id left join corn_futures cf on  ri.corn_future_id = cf.corn_future_id left join dbo.corn_futures_distributions cfd on  cf.corn_future_id = cfd.corn_future_id left join dbo.items_distributions id on  cfd.corn_future_distribution_id = id.corn_future_distribution_id where   pid.purchase_instruction_detail_id = ? group by pid.corn_quantity";
	private static final String CORN_ORIGIN_IMPORTED = "I";
	private static final String SQL_RELATED_RECORDS = "select sum(total) as total from (select count(*) as total from realizeds_instructions where corn_future_id = ? union all select count(*) from corn_futures_versions where corn_future_id = ? union all select count(*) from purchases_orders_futures where corn_future_id = ?) dummy";
	/**
	 * Insertar un FuturePurchase
	 * 
	 * @param fupDTO
	 * @return el Id del FuturePurchase Insertado
	 * @throws FuturePurchaseException
	 */
	public int insert(FuturePurchaseDTO fupDTO) throws FuturePurchaseException {
		int lastInserted = -1;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		String cornOrign = CORN_ORIGIN_IMPORTED;
		try {

			if (fupDTO != null) {
				pstm = prepareSQL(SQL_INSERT_FUTURE_PURCHASE, dsName);

				pstm.setInt(1, fupDTO.getAccountFuturePeriodId());
				pstm.setInt(2, fupDTO.getApplicationFuturePeriodId());
				pstm.setString(3, fupDTO.getReference());
				pstm.setInt(4, fupDTO.getQuantity());
				pstm.setDate(5, fupDTO.getPurchaseDate());
				pstm.setString(6, fupDTO.getUnitPrice());
				if( !StringUtils.isEmpty(fupDTO.getCornOrigin())){
					cornOrign = fupDTO.getCornOrigin();
				}
				pstm.setString(7, cornOrign);
				
				executeInsert(pstm);
				closeConnection(pstm.getConnection());
				pstm = prepareSQL(getLastInsertIdForTable("corn_futures"),
						dsName);
				rs = executeQuery(pstm);
				if (rs.next())
					lastInserted = rs.getInt(1);
			}

		} catch (SQLException sqle) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			sqle.printStackTrace();
			throw futurePurchaseException;
		} catch (Exception exception) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(exception, CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			exception.printStackTrace();
			throw futurePurchaseException;
		} finally {
			try {
				closeConnection(pstm.getConnection());
			} catch (SQLException sqle) {}
		}
		return lastInserted;
	}

	/**
	 * Actualizar un FuturePurchase
	 * 
	 * @param fupDTO
	 * @return numero de registros actualizados
	 * @throws FuturePurchaseException
	 */
	public int update(FuturePurchaseDTO fupDTO) throws FuturePurchaseException {
		int insertedRows = 0;
		PreparedStatement pstm = null;

		try {

			if (fupDTO != null) {
				pstm = prepareSQL(SQL_UPDATE_FUTURE_PURCHASE, dsName);

				pstm.setInt(1, fupDTO.getAccountFuturePeriodId());
				pstm.setInt(2, fupDTO.getApplicationFuturePeriodId());
				pstm.setString(3, fupDTO.getReference());
				pstm.setInt(4, fupDTO.getQuantity());
				pstm.setDate(5, fupDTO.getPurchaseDate());
				pstm.setString(6, fupDTO.getUnitPrice());
				pstm.setString(7, fupDTO.getCornOrigin());
				pstm.setInt(8, fupDTO.getCornFutureId());
				
				insertedRows = executeInsert(pstm);
			}

		} catch (SQLException sqle) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(
					sqle, CornPPException.LAYER_DAO,
					CornPPException.ACTION_INSERT);
			throw futurePurchaseException;
		} catch (Exception exception) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(
					exception, CornPPException.LAYER_DAO,
					CornPPException.ACTION_INSERT);
			throw futurePurchaseException;
		} finally {
			try {

				closeConnection(pstm.getConnection());

			} catch (SQLException sqle) {

			}
		}

		return insertedRows;
	}

	/**
	 * Delete future purchase
	 * 
	 * @param futurePurchaseId
	 *            Id to delete
	 * @throws FuturePurchaseException
	 */
	public void delete(int futurePurchaseId) throws FuturePurchaseException {
		PreparedStatement preparedStatement = null;
		SystemParameterCollection systemParameterCollection = null;
		SystemParameterBean systemParameterBean = null;
		try {
			preparedStatement = prepareSQL(SQL_DELETE_FUTURE_PURCHASE, dsName);
			preparedStatement.setInt(1, futurePurchaseId);
			executeUpdate(preparedStatement);
		} catch (PoolException poolException) {
			FuturePurchaseException futurePurchaseException = null;
			poolException.printStackTrace(System.out);
			futurePurchaseException = new FuturePurchaseException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw futurePurchaseException;
		} catch (SQLException sqle) {
			FuturePurchaseException futurePurchaseException = null;
			systemParameterCollection = SystemParameterCollection.getInstance();
			systemParameterBean = systemParameterCollection.getSystemParameterBean(DBDAO.FOREIGN_KEY_ERROR_CODE);
			if (sqle.getErrorCode() == systemParameterBean.getIntValue()) {
				futurePurchaseException = new FuturePurchaseException(DBDAO.FOREIGN_KEY_ERROR_KEY, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			} else {
				futurePurchaseException = new FuturePurchaseException(sqle, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
			}
			sqle.printStackTrace(System.out);
			throw futurePurchaseException;
		} finally {
			try {
				closeConnection(preparedStatement.getConnection());
			} catch (Exception exception) {
			}
		}
	}

	/**
	 * Delete future purchase
	 * 
	 * @param futurePurchaseId
	 *            Id to delete
	 * @throws FuturePurchaseException
	 */
	public void deleteDistribution(int cornFutureId, int cornFutureDistributionId)throws FuturePurchaseException {
		int deletedRows = 0;
		int relatedRecords = 0;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		SystemParameterCollection systemParameterCollection = null;
		SystemParameterBean systemParameterBean = null;
		FuturePurchaseException foreignException = null;
		try {
			preparedStatement = prepareSQL(SQL_RELATED_RECORDS, dsName);
			preparedStatement.setInt(1, cornFutureId);
			preparedStatement.setInt(2, cornFutureId);
			preparedStatement.setInt(3, cornFutureId);
			resultSet = executeQuery(preparedStatement);
			if( resultSet.next() ){
				relatedRecords = resultSet.getInt(1);
			}
			closeConnection(preparedStatement.getConnection());
			
			if( relatedRecords == 0 ){
				preparedStatement = prepareSQL(SQL_DELETE_ITEM_DISTRIBUTION_BY_CUSTOMER, dsName);
				preparedStatement.setInt(1, cornFutureDistributionId);
				executeUpdate(preparedStatement);
				closeConnection(preparedStatement.getConnection());
	
				preparedStatement = prepareSQL(SQL_DELETE_CUSTOMER_DISTRIBUTION_BY_CUSTOMER, dsName);
				preparedStatement.setInt(1, cornFutureDistributionId);
				deletedRows = executeUpdate(preparedStatement);
				closeConnection(preparedStatement.getConnection());
				
				//Borra la tabla de futuros si es que ya no existen clientes para dicho registro
				try{
					preparedStatement = prepareSQL(SQL_DELETE_FUTURE_PURCHASE, dsName);
					preparedStatement.setInt(1, cornFutureId);
					executeUpdate(preparedStatement);
				}catch(SQLException sqlException){
					systemParameterCollection = SystemParameterCollection.getInstance();
					systemParameterBean = systemParameterCollection.getSystemParameterBean(DBDAO.FOREIGN_KEY_ERROR_CODE);
					if (sqlException.getErrorCode() != systemParameterBean.getIntValue()) {
						throw sqlException;
					}
				}
			}else{
				foreignException = new FuturePurchaseException(DBDAO.FOREIGN_KEY_ERROR_KEY, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
				throw foreignException;
			}

			if (deletedRows <= 0) {
				FuturePurchaseException futurePurchaseException = null;
				futurePurchaseException = new FuturePurchaseException( MSG_NO_DELETE, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
				throw futurePurchaseException;
			}
		} catch(FuturePurchaseException futurePurchaseException){
			throw futurePurchaseException;
		} catch (PoolException poolException) {
			FuturePurchaseException futurePurchaseException = null;
			poolException.printStackTrace(System.out);
			futurePurchaseException = new FuturePurchaseException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw futurePurchaseException;
		} catch (SQLException sqle) {
			FuturePurchaseException futurePurchaseException = null;
			sqle.printStackTrace(System.out);
			futurePurchaseException = new FuturePurchaseException(sqle,CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
			throw futurePurchaseException;
		} finally {
			try {
				closeConnection(preparedStatement.getConnection());
			} catch (Exception exception) {
			}
		}
	}

	/**
	 * 
	 * @param beginPeriodYear
	 * @param endPeriodYear
	 * @return
	 * @throws FuturePurchaseException
	 */
	public List getFuturePurchasesByPeriodYearRange(
			FuturePurchaseSearchCriteriaDTO criteriaDTO, int orderField,
			boolean ascendingOrder) throws FuturePurchaseException {
		List futurePurchases = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		StringBuffer sql = null;

		try {
			futurePurchases = new ArrayList(0);
			sql = new StringBuffer(SQL_SELECT_FUTURE_PURCHASES_BY_PERIOD_RANGE);

			if (criteriaDTO.isWithoutProduct()) {
				if (criteriaDTO.isWithoutClient()) {
					sql.append(SQL_FILTER_WITHOUT_CUSTOMER);
				} else {
					sql.append(SQL_FILTER_WITHOUT_PRODUCT);
				}
			} else {
				if (criteriaDTO.isWithoutClient()) {
					sql.append(SQL_FILTER_WITHOUT_CUSTOMER);
				}
			}

			if (criteriaDTO.getClientDescription() != null
					&& !criteriaDTO.getClientDescription().trim().equals(
							StringUtils.EMPTY)) {
				sql.append(SQL_FILTER_CUSTOMER);
			}
			if (criteriaDTO.getProductDescription() != null
					&& !criteriaDTO.getProductDescription().trim().equals(
							StringUtils.EMPTY)) {
				sql.append(SQL_FILTER_PRODUCT);
			}

			sql.append(SQL_ORDER_BY);

			switch (orderField) {
			case 0:
				sql.append(FuturePurchaseDTO.DEFAULT_ORDER);
				break;
			case 1:
				sql.append(FuturePurchaseDTO.PERIOD_ACCOUNT);
				break;
			case 2:
				sql.append(FuturePurchaseDTO.QUANTITY);
				break;
			case 3:
				sql.append(FuturePurchaseDTO.UNIT_PRICE);
				break;
			case 4:
				sql.append(FuturePurchaseDTO.PRICE);
				break;
			case 5:
				sql.append(FuturePurchaseDTO.CORN_ORIGIN);
				break;

			}

			if (ascendingOrder) {
				sql.append(SQL_ASCENDING_ORDER);
			} else {
				sql.append(SQL_DESCENDING_ORDER);
			}

			pstm = prepareSQL(sql.toString(), dsName);

			pstm.setInt(1, criteriaDTO.getBeginPeriodYear());
			pstm.setInt(2, criteriaDTO.getEndPeriodYear());
			if (criteriaDTO.getClientDescription() != null
					&& !criteriaDTO.getClientDescription().trim().equals(
							StringUtils.EMPTY)) {
				pstm
						.setString(
								3,
								"%"
										+ (criteriaDTO.getClientDescription() != null ? criteriaDTO
												.getClientDescription().trim()
												: StringUtils.EMPTY) + "%");
				pstm
						.setString(
								4,
								"%"
										+ (criteriaDTO.getClientDescription() != null ? criteriaDTO
												.getClientDescription().trim()
												: StringUtils.EMPTY) + "%");
			}
			if (criteriaDTO.getProductDescription() != null
					&& !criteriaDTO.getProductDescription().trim().equals(
							StringUtils.EMPTY)) {
				if (criteriaDTO.getClientDescription() != null
						&& !criteriaDTO.getClientDescription().trim().equals(
								StringUtils.EMPTY)) {
					pstm
							.setString(
									5,
									"%"
											+ (criteriaDTO
													.getProductDescription() != null ? criteriaDTO
													.getProductDescription()
													.trim()
													: StringUtils.EMPTY) + "%");
					pstm
							.setString(
									6,
									"%"
											+ (criteriaDTO
													.getProductDescription() != null ? criteriaDTO
													.getProductDescription()
													.trim()
													: StringUtils.EMPTY) + "%");
				} else {
					pstm
							.setString(
									3,
									"%"
											+ (criteriaDTO
													.getProductDescription() != null ? criteriaDTO
													.getProductDescription()
													.trim()
													: StringUtils.EMPTY) + "%");
					pstm
							.setString(
									4,
									"%"
											+ (criteriaDTO
													.getProductDescription() != null ? criteriaDTO
													.getProductDescription()
													.trim()
													: StringUtils.EMPTY) + "%");
				}
			}

			rs = executeQuery(pstm);

			futurePurchases = mapRows(rs);

		} catch (SQLException sqle) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(
					sqle, CornPPException.LAYER_DAO,
					CornPPException.ACTION_LISTS);
			sqle.printStackTrace(System.out);
			throw futurePurchaseException;
		} catch (Exception exception) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(
					exception, CornPPException.LAYER_DAO,
					CornPPException.ACTION_LISTS);
			exception.printStackTrace(System.out);
			throw futurePurchaseException;
		} finally {

			try {

				closeConnection(pstm.getConnection());

			} catch (SQLException sqle) {

			}
		}

		return futurePurchases;
	}

	public FuturePurchaseDTO getFuturePurchaseByDistributionId(
			int cornFutureDistributionId) throws FuturePurchaseException {
		FuturePurchaseDTO futurePurchase = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		try {
			pstm = prepareSQL(SQL_SELECT_FUTURE_PURCHASES_BY_DISTRIBUTION_ID,
					dsName);
			pstm.setInt(1, cornFutureDistributionId);
			rs = executeQuery(pstm);
			if (rs.next()) {
				futurePurchase = mapRow(rs, 1);
			}
		} catch (PoolException poolException) {
			FuturePurchaseException futurePurchaseException = null;
			poolException.printStackTrace(System.out);
			futurePurchaseException = new FuturePurchaseException(
					poolException, CornPPException.LAYER_DAO,
					CornPPException.ACTION_SELECT);
			throw futurePurchaseException;
		} catch (SQLException sqlException) {
			FuturePurchaseException futurePurchaseException = null;
			sqlException.printStackTrace(System.out);
			futurePurchaseException = new FuturePurchaseException(sqlException,
					CornPPException.LAYER_DAO, CornPPException.ACTION_SELECT);
			throw futurePurchaseException;
		} finally {
			try {
				closeConnection(pstm.getConnection());
			} catch (SQLException sqle) {
			}
		}
		return futurePurchase;
	}

	/**
	 * 
	 * @param fupDTO
	 * @return
	 * @throws FuturePurchaseException
	 */
	public FuturePurchaseDTO getFuturePurchaseById(FuturePurchaseDTO fupDTO)
			throws FuturePurchaseException {
		FuturePurchaseDTO futurePurchase = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		try {

			if (fupDTO != null) {
				pstm = prepareSQL(SQL_SELECT_FUTURE_PURCHASES_BY_ID, dsName);

				pstm.setInt(1, fupDTO.getCornFutureId());

				rs = executeQuery(pstm);
				if (rs.next())
					futurePurchase = mapRow(rs, 1);
			}

		} catch (PoolException poolException) {
			FuturePurchaseException futurePurchaseException = null;
			poolException.printStackTrace(System.out);
			futurePurchaseException = new FuturePurchaseException(
					poolException, CornPPException.LAYER_DAO,
					CornPPException.ACTION_SELECT);
			throw futurePurchaseException;
		} catch (SQLException sqlException) {
			FuturePurchaseException futurePurchaseException = null;
			sqlException.printStackTrace(System.out);
			futurePurchaseException = new FuturePurchaseException(sqlException,
					CornPPException.LAYER_DAO, CornPPException.ACTION_SELECT);
			throw futurePurchaseException;
		} finally {

			try {

				closeConnection(pstm.getConnection());

			} catch (SQLException sqle) {

			}
		}

		return futurePurchase;
	}

	public List getFuturePurchasesByAvailable(int purchaseOrderId,
			int orderByColumn, boolean ascendingOrder)
			throws FuturePurchaseException {
		StringBuffer sql = null;
		List futurePurchases = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		FuturePurchaseDTO futurePurchaseDTO = null;
		boolean futureAssigned = false;

		sql = new StringBuffer(SQL_SELECT_FUTURE_PURCHASES_BY_AVAILABLES);
		sql.append(SQL_ORDER_BY);

		switch (orderByColumn) {
		case 0:
			sql.append(FuturePurchaseDTO.APPLICATION_FUTURE_PERIOD_NAME);
			if (ascendingOrder) {
				sql.append(SQL_ASCENDING_ORDER);
			} else {
				sql.append(SQL_DESCENDING_ORDER);
			}
			sql.append(com.cs.common.utils.StringUtils.COMMA);
			sql.append(FuturePurchaseDTO.UNITPRICE);
			break;
		case 1:
			sql.append(FuturePurchaseDTO.ACCOUNT_FUTURE_PERIOD_NAME);
			break;
		case 2:
			sql.append(FuturePurchaseDTO.REFERENCE);
			break;
		case 3:
			sql.append(FuturePurchaseDTO.TOTAL_QUANTITY);
			break;
		case 4:
			sql.append(FuturePurchaseDTO.AVAILABLE);
			break;
		case 5:
			sql.append(FuturePurchaseDTO.UNITPRICE);
			break;
		}
		if( orderByColumn == 0 ){
			sql.append(SQL_DESCENDING_ORDER);
		}else{
			if (ascendingOrder) {
				sql.append(SQL_ASCENDING_ORDER);
			} else {
				sql.append(SQL_DESCENDING_ORDER);
			}
		}
		try {
			futurePurchases = new ArrayList(0);
			pstm = prepareSQL(sql.toString(), dsName);
			pstm.setInt(1, purchaseOrderId);
			rs = executeQuery(pstm);
			while (rs.next()) {
				futureAssigned = false;
				futurePurchaseDTO = new FuturePurchaseDTO();
				futurePurchaseDTO.setCornFutureId(rs.getInt(1));
				futurePurchaseDTO.setApplicationFuturePeriodId(rs.getInt(2));
				futurePurchaseDTO.setAccountFuturePeriodId(rs.getInt(3));
				futurePurchaseDTO.setReference(rs.getString(4));
				futurePurchaseDTO.setQuantity(rs.getInt(5));
				futurePurchaseDTO.setPurchaseDate(rs.getDate(6));
				futurePurchaseDTO.setUnitPrice(rs.getString(7));
				futurePurchaseDTO.setUpdated(rs.getDate(8));
				futurePurchaseDTO.setApplicationFuturePeriodName(rs
						.getString(9));
				futurePurchaseDTO.setAccountFuturePeriodName(rs.getString(10));
				futurePurchaseDTO.setAvailables(new Double(rs.getDouble(11)));
				futurePurchaseDTO.setQuantityAssigned(rs.getDouble(12));
				if (rs.getInt(13) > -1) {
					futureAssigned = true;
				}
				futurePurchaseDTO.setFutureAssigned(futureAssigned);
				futurePurchases.add(futurePurchaseDTO);
			}
		} catch (SQLException sqle) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(
					sqle, CornPPException.LAYER_DAO,
					CornPPException.ACTION_LISTS);
			sqle.printStackTrace(System.out);
			throw futurePurchaseException;
		} catch (Exception exception) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(
					exception, CornPPException.LAYER_DAO,
					CornPPException.ACTION_LISTS);
			exception.printStackTrace(System.out);
			throw futurePurchaseException;
		} finally {
			try {
				closeConnection(pstm.getConnection());
			} catch (SQLException sqle) {
			}
		}

		return futurePurchases;
	}

	/**
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private List mapRows(ResultSet rs) throws SQLException {
		List futurePurchases = null;

		futurePurchases = new ArrayList(0);

		if (rs != null) {

			for (; rs.next();) {
				futurePurchases.add(mapRow(rs, rs.getRow()));
			}
		}

		return futurePurchases;
	}

	private FuturePurchaseDTO mapRow(ResultSet rs, int rowIndex)
			throws SQLException {
		FuturePurchaseDTO futurePurchaseDTO = null;

		if ((rs != null) && (rs.absolute(rowIndex))) {
			futurePurchaseDTO = new FuturePurchaseDTO();

			futurePurchaseDTO.setCornFutureId(rs.getInt(1));
			futurePurchaseDTO.setApplicationFuturePeriodId(rs.getInt(2));
			futurePurchaseDTO.setAccountFuturePeriodId(rs.getInt(3));
			futurePurchaseDTO.setReference(rs.getString(4));
			futurePurchaseDTO.setQuantity(rs.getInt(5));
			futurePurchaseDTO.setPurchaseDate(rs.getDate(6));
			futurePurchaseDTO.setUnitPrice(rs.getString(7));
			futurePurchaseDTO.setUpdated(rs.getDate(8));
			futurePurchaseDTO.setApplicationFuturePeriodName(rs.getString(9));
			futurePurchaseDTO.setAccountFuturePeriodName(rs.getString(10));
			futurePurchaseDTO.setAvailables(new Double(rs.getDouble(11)));
			futurePurchaseDTO.setPrice(rs.getDouble(12));
			futurePurchaseDTO.setCornOrigin(rs.getString(13));
		}

		return futurePurchaseDTO;
	}

	/**
	 * Listado de Compras Realizadas
	 * 
	 * @author Edgar Joao
	 * @param id
	 * @param orderByColumn
	 * @param ascendingOrder
	 * @return
	 * @throws FuturePurchaseException
	 */
	public List getFuturePurchasesRealized(int id, int orderByColumn,
			boolean ascendingOrder) throws FuturePurchaseException {
		StringBuffer sql = null;
		List futurePurchases = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		FuturePurchaseDTO futurePurchaseDTO = null;

		sql = new StringBuffer(SQL_SELECT_FUTURE_PURCHASES_REALIZED);
		sql.append(SQL_ORDER_BY);

		switch (orderByColumn) {
		case 0:
			sql.append(FuturePurchaseDTO.QUANTITY);
			break;
		case 1:
			sql.append(FuturePurchaseDTO.PRICE);
			break;
		case 2:
			sql.append(FuturePurchaseDTO.ACCOUNT_FUTURE_PERIOD_NAME);
			break;
		case 3:
			sql.append(FuturePurchaseDTO.APPLICATION_FUTURE_PERIOD_NAME);
			break;
		case 4:
			sql.append(FuturePurchaseDTO.REFERENCE);
			break;
		}
		if (ascendingOrder) {
			sql.append(SQL_ASCENDING_ORDER);
		} else {
			sql.append(SQL_DESCENDING_ORDER);
		}
		try {
			futurePurchases = new ArrayList(0);
			pstm = prepareSQL(sql.toString(), dsName);
			pstm.setInt(1, id);
			rs = executeQuery(pstm);
			while (rs.next()) {
				futurePurchaseDTO = new FuturePurchaseDTO();
				futurePurchaseDTO.setCornFutureId(rs.getInt(1));
				futurePurchaseDTO.setApplicationFuturePeriodId(rs.getInt(2));
				futurePurchaseDTO.setAccountFuturePeriodId(rs.getInt(3));
				futurePurchaseDTO.setReference(rs.getString(4));
				futurePurchaseDTO.setQuantity(rs.getInt(5));
				futurePurchaseDTO.setPurchaseDate(rs.getDate(6));
				futurePurchaseDTO.setUnitPrice(rs.getString(7));
				futurePurchaseDTO.setUpdated(rs.getDate(8));
				futurePurchaseDTO.setAccountFuturePeriodName(rs.getString(9));
				futurePurchaseDTO.setApplicationFuturePeriodName(rs
						.getString(10));
				futurePurchaseDTO.setAvailables(new Double(rs.getDouble(11)));
				futurePurchases.add(futurePurchaseDTO);
			}
		} catch (SQLException sqle) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(
					sqle, CornPPException.LAYER_DAO,
					CornPPException.ACTION_LISTS);
			sqle.printStackTrace(System.out);
			throw futurePurchaseException;
		} catch (Exception exception) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(
					exception, CornPPException.LAYER_DAO,
					CornPPException.ACTION_LISTS);
			exception.printStackTrace(System.out);
			throw futurePurchaseException;
		} finally {
			try {
				closeConnection(pstm.getConnection());
			} catch (SQLException sqle) {
			}
		}
		return futurePurchases;
	}
	
	public void insertPurchasesRealized(FuturePurchaseRealizedDTO futurePurchaseRealizedDTO) throws FuturePurchaseException {
		PreparedStatement pstm = null;
		try{
			pstm = prepareSQL(SQL_INSERT_REALIZED_PURCHASES, dsName);
			pstm.setInt(1, futurePurchaseRealizedDTO.getCornFutureId());
			pstm.setInt(2, futurePurchaseRealizedDTO.getPurchaseInstructionFutureId());
			pstm.setString(3, futurePurchaseRealizedDTO.getUserId());
			pstm.execute();
		} catch (SQLException sqle) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			sqle.printStackTrace(System.out);
			throw futurePurchaseException;
		} catch (Exception exception) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(exception, CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			exception.printStackTrace(System.out);
			throw futurePurchaseException;
		} finally {
			try {
				closeConnection(pstm.getConnection());
			} catch (SQLException sqle) {
			}
		}
	}
	
	public int remainingTons(int purchaseInstructionDetailId)throws FuturePurchaseException{
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int remainingTons = 0;
		try{
			preparedStatement = prepareSQL(SQL_GET_REMAINING_TONS, dsName);
			preparedStatement.setInt(1, purchaseInstructionDetailId);
			resultSet = preparedStatement.executeQuery();
			if( resultSet.next() ){
				remainingTons = resultSet.getInt(1);
			}
		} catch (SQLException sqle) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			sqle.printStackTrace(System.out);
			throw futurePurchaseException;
		} catch (Exception exception) {
			FuturePurchaseException futurePurchaseException = new FuturePurchaseException(exception, CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			exception.printStackTrace(System.out);
			throw futurePurchaseException;
		} finally {
			try {
				closeConnection(preparedStatement.getConnection());
			} catch (SQLException sqle) {
			}
		}
		return remainingTons;
	}
}